package vip.xiaonuo.smzq.core.tools.excel;

import cn.hutool.core.bean.BeanUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import vip.xiaonuo.smzq.modular.hisi.entity.vo.Kcdgmtj;
import vip.xiaonuo.smzq.modular.hisi.entity.vo.KcdgmtjVo;
import vip.xiaonuo.smzq.modular.hisi.entity.vo.Ksxicx;
import vip.xiaonuo.smzq.modular.hisi.entity.vo.Zyclcx;
import vip.xiaonuo.smzq.modular.jsc.entity.vo.Dcexcelkccb;
import vip.xiaonuo.smzq.modular.kcl.entity.Sjyj;
import vip.xiaonuo.smzq.modular.nbfb.entity.vo.NbfbFiveVo;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;

public class ExcelExporter {
    /**
     * 矿山和矿产地信息查询
     *
     * @param dataList      查询的数据
     * @param dynamicHeader 动态表头
     * @throws SQLException
     * @throws IOException
     */
    public static void exportKsandKcd(List<Ksxicx> dataList, List<List<String>> dynamicHeader, HttpServletResponse response, Integer type) throws SQLException, IOException {
        // 创建一个XSSFWorkbook对象，表示.xlsx格式的工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = null;
        if (type == 1) {
            sheet = workbook.createSheet("矿山信息");
            // writeSheet = EasyExcel.writerSheet("矿山信息").build();
        } else if (type == 2) {
            sheet = workbook.createSheet("矿产地信息");
        }

        PrintSetup printSetup = sheet.getPrintSetup();
        printSetup.setPaperSize(PrintSetup.A3_PAPERSIZE); // 设置纸张大小为A3
        printSetup.setLandscape(true); // 设置方向为横向

        // 创建第一个行表头
        Row row = sheet.createRow(0);
        for (int j = 0; j < dynamicHeader.get(0).size(); j++) {
            String fieldName = dynamicHeader.get(0).get(j);
            // 创建一个单元格对象
            Cell cell = row.createCell(j);
            // 设置单元格的值
            cell.setCellValue(fieldName);
            // 开启当前单元格的自动换行功能
            cell.setCellStyle(autoWrapCellStyle(workbook));
        }


        List<List<Object>> data = dataList.stream()
                .map(bean -> convertBeanToRowData(bean, dynamicHeader.get(1)))
                .collect(Collectors.toList());

        //添加内容
        for (int i = 0; i < data.size(); i++) {
            // 创建每一行数据
            Row rowData = sheet.createRow(i + 1);
            //每一行数据
            // Map<String,String> map=mapList.get(i);
            List<Object> list = data.get(i);

            for (int j = 0; j < list.size(); j++) {
                String fieldName = String.valueOf(list.get(j));
                // 创建一个单元格对象
                Cell cell = rowData.createCell(j);
                // 设置单元格的值
                cell.setCellValue(fieldName);


            }
        }

        // 写入数据后，调整所有列的宽度以适应内容
        for (int i = 0; i < dynamicHeader.get(0).size(); i++) {
            sheet.autoSizeColumn(i); // 自动调整列宽
        }
        workbook.write(response.getOutputStream());
    }

    // 方法用于设置自动换行的单元格样式
    private static CellStyle autoWrapCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setWrapText(true); // 启用自动换行
        return style;
    }

    private static List<Object> convertBeanToRowData(Ksxicx bean, List<String> dynamicHeader) {
        return dynamicHeader.stream()
                .map(header -> {
                    switch (header) {
                        case "xzqhmc"://行政区划
                            return bean.getXzqhmc();
                        case "nd"://年度
                            return bean.getNd();
                        case "kqbh"://矿区编号
                            return bean.getKqbh();
                        case "kqmc"://矿区名称
                            return bean.getKqmc();
                        case "djflbh"://分类登记编号
                            return bean.getDjflbh();
                        case "ksbh"://矿山编号
                            return bean.getKsbh();
                        case "ksmc"://矿山名称
                            return bean.getKsmc();
                        case "jzyxq"://截止有效期
                            return bean.getJzyxq();
                        case "xkzh"://许可证号
                            return bean.getXkzh();
                        case "kyqr"://矿业权人
                            return bean.getKyqr();
                        case "kcmc"://矿产名称
                            return bean.getKcmc();
                        case "xzqdm"://行政区代码
                            return bean.getXzqdm();
                        case "ksldw"://矿石量单位
                            return bean.getKsldw();
                        case "jsldw"://金属量单位
                            return bean.getJsldw();
                        case "ksbyl"://矿山保有量
                            return bean.getKsbyl();
                        case "ksljl"://矿山累计量
                            return bean.getKsljl();
                        case "kskcl"://矿山开采量
                            return bean.getKskcl();
                        case "ksssl"://矿山损失量
                            return bean.getKsssl();
                        case "kskczjl"://矿山勘查增减
                            return bean.getKskczjl();
                        case "kscszjl"://矿山重算增减
                            return bean.getKscszjl();
                        case "ksksl"://矿山矿石量
                            return bean.getKsksl();
                        case "kqbyl"://矿区保有量
                            return bean.getKqbyl();
                        case "kqljl"://矿区累计量
                            return bean.getKqljl();
                        case "kqkcl"://矿区开采量
                            return bean.getKqkcl();
                        case "kqssl"://矿区损失量
                            return bean.getKqssl();
                        case "kqkczjl"://矿区勘查增减
                            return bean.getKqkczjl();
                        case "ksspyfl"://矿山审批压覆量
                            return bean.getKsspyfl();
                        case "kqspyal"://矿区审批压覆量
                            return bean.getKqspyal();
                        case "ksqtl"://矿山其他量
                            return bean.getKsqtl();
                        case "kqqtl"://矿区其他量
                            return bean.getKqqtl();
                        case "kqcszjl"://矿区重算增减
                            return bean.getKqcszjl();
                        case "kqksl"://矿区矿石量
                            return bean.getKqksl();
                        case "kqzsl"://矿区证实量
                            return bean.getKqzsl();
                        case "kqkxl"://矿区可信量
                            return bean.getKqkxl();
                        case "kqtml"://矿区探明量
                            return bean.getKqtml();
                        case "kqkzl"://矿区控制量
                            return bean.getKqkzl();
                        case "kqtdl"://矿区推断量
                            return bean.getKqtdl();
                        case "kcjdmc"://勘察阶段名称
                            return bean.getKcjdmc();
                        case "kcqk"://开采情况(0未开采，1开采)
                            if (bean.getKcqk() == 0) {
                                return "未开采";
                            } else if (bean.getKcqk() == 1) {
                                return "开采";
                            }
                        case "sfcj"://是否出界（0未出界，1出界）
                            if (bean.getSfcj() == 0) {
                                return "未出界";
                            } else if (bean.getSfcj() == 1) {
                                return "出界";
                            }
                            //return bean.getJsldw();
                        case "gm"://规模
                            return bean.getGm();
                        case "zczh"://矿产组合
                            return bean.getZczh();
                        case "sfly"://是否利用（0未利用，1利用）
                            if (bean.getSfly() == 0) {
                                return "未利用";
                            } else if (bean.getSfly() == 1) {
                                return "利用";
                            }

                        default:
                            return null;
                    }
                })
                .collect(Collectors.toList());
    }


    /**
     * 矿产地规模查询
     *
     * @param dataList      查询的数据
     * @param dynamicHeader 动态表头
     * @throws SQLException
     * @throws IOException
     */
    public static void exportKcdGm(List<KcdgmtjVo> dataList, List<List<String>> dynamicHeader, HttpServletResponse response) throws SQLException, IOException {

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("矿产地规模");
        // 创建第一个行表头
        Row row = sheet.createRow(0);
        for (int j = 0; j < dynamicHeader.get(0).size(); j++) {
            String fieldName = dynamicHeader.get(0).get(j);
            // 创建一个单元格对象
            Cell cell = row.createCell(j);
            // 设置单元格的值
            cell.setCellValue(fieldName);
        }
        List<List<Object>> data = dataList.stream()
                .map(bean -> convertBeanToRowDataKcdGm(bean, dynamicHeader.get(1)))
                .collect(Collectors.toList());
        //添加内容
        for (int i = 0; i < data.size(); i++) {
            // 创建每一行数据
            Row rowData = sheet.createRow(i + 1);
            //每一行数据
            // Map<String,String> map=mapList.get(i);
            List<Object> list = data.get(i);

            for (int j = 0; j < list.size(); j++) {
                String fieldName = String.valueOf(list.get(j));
                // 创建一个单元格对象
                Cell cell = rowData.createCell(j);
                // 设置单元格的值
                cell.setCellValue(fieldName);

            }
        }
        workbook.write(response.getOutputStream());

    }

    private static List<Object> convertBeanToRowDataKcdGm(KcdgmtjVo bean, List<String> dynamicHeader) {
        return dynamicHeader.stream()
                .map(header -> {
                    switch (header) {
                        case "xzqhmc"://行政区划
                            return bean.getXzqhmc();
//                        case "xzqhdm"://行政区代码
//                            return bean.getXzqhdm();
                        case "bigCount"://大型矿产地数量
                            return bean.getBigCount();
                        case "bigNmbyjsl"://大型金属量
                            return bean.getBigNmbyjsl();
                        case "bigNmbyksl"://大型矿石量
                            return bean.getBigNmbyksl();
                        case "mediumCount"://中型矿产地数量
                            return bean.getMediumCount();
                        case "mediumNmbyjsl"://中型金属量
                            return bean.getMediumNmbyjsl();
                        case "mediumNmbyksl"://中型矿石量
                            return bean.getMediumNmbyksl();
                        case "smallCount"://小型矿产地数量
                            return bean.getSmallCount();
                        case "smallNmbyjsl"://小型金属量
                            return bean.getSmallNmbyjsl();
                        case "smallNmbyksl"://小型矿石量
                            return bean.getSmallNmbyksl();
                        case "kcmc"://矿种名称
                            return bean.getKcmc();
                        case "jsdxmc"://计算对象名称
                            return bean.getJsdxmc();
                        case "bigNmby"://大型矿保有
                            return bean.getBigNmby();
                        case "mediumNmby"://中型矿保有量
                            return bean.getMediumNmby();
                        case "smallNmby"://小型矿保有量
                            return bean.getSmallNmby();
                        default:
                            return null;
                    }
                })
                .collect(Collectors.toList());
    }


    /**
     * 资源储量查询
     *
     * @param dataList      查询的数据
     * @param dynamicHeader 动态表头
     * @throws SQLException
     * @throws IOException
     */
    public static void exportZyclcx(List<Zyclcx> dataList, List<List<String>> dynamicHeader, HttpServletResponse response) throws SQLException, IOException {

//        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), Zyclcx.class).build();
//        WriteSheet writeSheet = EasyExcel.writerSheet("资源储量").build();
//
//        List<List<String>> headData = new ArrayList<>();
//        headData.add(dynamicHeader.get(0));
//
//        List<List<Object>> data = dataList.stream()
//                .map(bean -> convertBeanToRowDataZyclcx(bean, dynamicHeader.get(1)))
//                .collect(Collectors.toList());
//
//        excelWriter.write(headData, (WriteSheet) data);
//        excelWriter.finish();
//        response.setContentType("application/vnd.ms-excel");
//        response.setHeader("ContentType", "application/vnd.ms-excel");
//        //response.setHeader("Content-Disposition", "attachment; filename=111.xlsx");
//        response.setCharacterEncoding("UTF-8");

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("资源储量");
        // 创建第一个行表头
        Row row = sheet.createRow(0);
        for (int j = 0; j < dynamicHeader.get(0).size(); j++) {
            String fieldName = dynamicHeader.get(0).get(j);
            // 创建一个单元格对象
            Cell cell = row.createCell(j);
            // 设置单元格的值
            cell.setCellValue(fieldName);
        }
        List<List<Object>> data = dataList.stream()
                .map(bean -> convertBeanToRowDataZyclcx(bean, dynamicHeader.get(1)))
                .collect(Collectors.toList());

        //添加内容
        for (int i = 0; i < data.size(); i++) {
            // 创建每一行数据
            Row rowData = sheet.createRow(i + 1);
            //每一行数据
            // Map<String,String> map=mapList.get(i);
            List<Object> list = data.get(i);

            for (int j = 0; j < list.size(); j++) {
                String fieldName = String.valueOf(list.get(j));
                // 创建一个单元格对象
                Cell cell = rowData.createCell(j);
                // 设置单元格的值
                cell.setCellValue(fieldName);

            }
        }
        workbook.write(response.getOutputStream());


    }

    /**
     * 资源储量查询
     *
     * @param dataList      查询的数据
     * @param dynamicHeader 动态表头
     * @throws SQLException
     * @throws IOException
     */
    public static void exportZyclcx2(List<Dcexcelkccb> dataList, List<List<String>> dynamicHeader, HttpServletResponse response) throws SQLException, IOException {

//        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), Zyclcx.class).build();
//        WriteSheet writeSheet = EasyExcel.writerSheet("资源储量").build();
//
//        List<List<String>> headData = new ArrayList<>();
//        headData.add(dynamicHeader.get(0));
//
//        List<List<Object>> data = dataList.stream()
//                .map(bean -> convertBeanToRowDataZyclcx(bean, dynamicHeader.get(1)))
//                .collect(Collectors.toList());
//
//        excelWriter.write(headData, (WriteSheet) data);
//        excelWriter.finish();
//        response.setContentType("application/vnd.ms-excel");
//        response.setHeader("ContentType", "application/vnd.ms-excel");
//        //response.setHeader("Content-Disposition", "attachment; filename=111.xlsx");
//        response.setCharacterEncoding("UTF-8");

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("资源储量");
        // 创建第一个行表头
        Row row = sheet.createRow(0);
        for (int j = 0; j < dynamicHeader.get(0).size(); j++) {
            String fieldName = dynamicHeader.get(0).get(j);
            // 创建一个单元格对象
            Cell cell = row.createCell(j);
            // 设置单元格的值
            cell.setCellValue(fieldName);
        }
        List<List<Object>> data = dataList.stream()
                .map(bean -> convertBeanToRowDataZyclcx2(bean, dynamicHeader.get(1)))
                .collect(Collectors.toList());

        //添加内容
        for (int i = 0; i < data.size(); i++) {
            // 创建每一行数据
            Row rowData = sheet.createRow(i + 1);
            //每一行数据
            // Map<String,String> map=mapList.get(i);
            List<Object> list = data.get(i);

            for (int j = 0; j < list.size(); j++) {
                String fieldName = String.valueOf(list.get(j));
                // 创建一个单元格对象
                Cell cell = rowData.createCell(j);
                // 设置单元格的值
                cell.setCellValue(fieldName);

            }
        }
        workbook.write(response.getOutputStream());


    }

    private static List<Object> convertBeanToRowDataZyclcx(Zyclcx bean, List<String> dynamicHeader) {
        return dynamicHeader.stream()
                .map(header -> {
                    switch (header) {
                        case "xzqhmc"://行政区划
                            return bean.getXzqhmc();
                        case "nd"://年度
                            return bean.getNd();
                        case "kqbh"://矿区编号
                            return bean.getKqbh();
                        case "kqmc"://矿区名称
                            return bean.getKqmc();
                        case "kzmc"://矿种代码
                            return bean.getKzmc();
                        case "jsldw"://金属量单位
                            return bean.getJsldw();
                        case "ksldw"://矿石量单位
                            return bean.getKsldw();
                        case "zsl"://证实量
                            return bean.getZsl();
                        case "kxl"://可信量
                            return bean.getKxl();
                        case "zskxhjl"://证实可信合计
                            return bean.getZskxhjl();
                        case "tml"://探明量
                            return bean.getTml();
                        case "kzl"://控制量
                            return bean.getKzl();
                        case "tdl"://推断量
                            return bean.getTdl();
                        case "tmkztdl"://探明，控制，推断合计量
                            return bean.getTmkztdl();
                        default:
                            return null;
                    }
                })
                .collect(Collectors.toList());
    }

    private static List<Object> convertBeanToRowDataZyclcx2(Dcexcelkccb bean, List<String> dynamicHeader) {
        return dynamicHeader.stream()
                .map(header -> {
                    switch (header) {
                        case "kcmc"://行政区划
                            return bean.getKcmc();
                        case "kcdm"://年度
                            return bean.getKcdm();
                        case "kctjdw"://矿区编号
                            return bean.getKctjdw();
                        case "kctbdw"://矿区名称
                            return bean.getKctbdw();
                        case "dwjz"://矿种代码
                            return bean.getDwjz();
                        case "xzqhdm"://金属量单位
                            return bean.getXzqhdm();
                        case "xzqhmc"://矿石量单位
                            return bean.getXzqhmc();
                        case "kcsl"://证实量
                            return bean.getKcsl();
                        case "zyltj"://可信量
                            return bean.getZyltj();
                        default:
                            return null;
                    }
                })
                .collect(Collectors.toList());
    }

    /**
     * 多年年初年末保有量的变化量
     *
     * @param dataList      查询的数据
     * @param dynamicHeader 动态表头
     * @throws SQLException
     * @throws IOException
     */
    public static void exportZyclBhl(List<Map<String, Object>> dataList, List<List<String>> dynamicHeader,String sheetName, HttpServletResponse response) throws SQLException, IOException {

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(sheetName);


        // 创建第一个行表头
        Row row = sheet.createRow(0);
        for (int j = 0; j < dynamicHeader.get(0).size(); j++) {
            String fieldName = dynamicHeader.get(0).get(j);
            // 创建一个单元格对象
            Cell cell = row.createCell(j);
            // 设置单元格的值
            cell.setCellValue(fieldName);
        }
        //添加内容
        for (int i = 0; i < dataList.size(); i++) {
            // 创建每一行数据
            Row rowData = sheet.createRow(i + 1);
            //每一行数据
            Map<String, Object> map = dataList.get(i);

            //遍历map给单元格赋值
            int m = 0;
            for (String key : dynamicHeader.get(1)) {
                if (map.containsKey(key)) {
                    // 创建一个单元格对象
                    Cell cell = rowData.createCell(m);

                   if (key.equals("kcqk")){
                       if (map.get(key)!=null&&!map.get(key).equals("")){
                           if (map.get(key).equals(1)){
                               cell.setCellValue("开采");
                           }else{
                               cell.setCellValue("未开采");
                           }
                       }
                   } else  if (key.equals("sfcj")){
                       if (map.get(key)!=null&&!map.get(key).equals("")){
                           if (map.get(key).equals(1)){
                               cell.setCellValue("出界");
                           }else{
                               cell.setCellValue("未出界");
                           }
                       }
                   }else if (key.equals("sfly")){
                       if (map.get(key)!=null&&!map.get(key).equals("")){
                           if (map.get(key).equals(1)){
                               cell.setCellValue("利用");
                           }else{
                               cell.setCellValue("未利用");
                           }
                       }
                   }else{
                       String value = "";
                       if (map.get(key)!=null){
                           value= map.get(key).toString();
                       }
                       // 设置单元格的值
                       cell.setCellValue(value);
                   }
                    m++;
                }
            }
        }
        workbook.write(response.getOutputStream());
    }

    /**
    * @description 导出矿山数据预警
    * @param dataList 数据源
    * @author bigStrong
    * @date 2024/7/8 11:38
    */
    public static void exportKsyj(List<Sjyj> dataList, HttpServletResponse response) throws SQLException, IOException {

        List<String> titleNameHeader = Arrays.asList(new String[]{"年度", "行政区划", "登记分类编号", "矿产名称", "矿区编号","矿山编号", "矿山名称", "矿业权人", "数据预警"});
        List<String> titleCodeHeader = Arrays.asList(new String[]{"nd", "xzqhmc", "djflbh", "kcmc", "kqbh","ksbh", "ksmc", "kyqr", "sjyjlx"});

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("矿山数据预警");

        // 创建第一个行表头
        Row row = sheet.createRow(0);
        for (int j = 0; j < titleNameHeader.size(); j++) {
            String fieldName = titleNameHeader.get(j);
            // 创建一个单元格对象
            Cell cell = row.createCell(j);
            // 设置单元格的值
            cell.setCellValue(fieldName);
        }

        for (int i = 0; i < dataList.size(); i++) {
            // 创建每一行数据
            Row rowData = sheet.createRow(i + 1);
            //每一行数据
            Sjyj sjyj = dataList.get(i);
            Map<String, Object> sjyjObj = BeanUtil.beanToMap(sjyj);
            //遍历map给单元格赋值
            int m = 0;
            for (String key : titleCodeHeader) {
                if (sjyjObj.containsKey(key)) {
                    // 创建一个单元格对象
                    Cell cell = rowData.createCell(m);
                    // 设置单元格的值
                    cell.setCellValue(String.valueOf(sjyjObj.get(key)));
                    m++;
                }
           }
       }

        workbook.write(response.getOutputStream());
    }

    /**
     * @description 年报附表5导出
     * @param dataList 数据源
     * @author Sqq
     * @date 2024/7/8 11:38
     */
    public static void exportNbfbFile(List<NbfbFiveVo> dataList, String header, String sheetName, HttpServletResponse response) throws SQLException, IOException {

        List<String> titleNameHeader = Arrays.asList(new String[]{"序号", "矿产名称", "单位", "矿产地数量", "资源储量类型", header, "保有", "累计查明", "采出量", "损失量", "勘查增减(±)量", "重算增减(±)量", "审批压覆量", "其他", "总计", "增减百分率（%）"});
        List<String> titleCodeHeader = Arrays.asList(new String[]{"num", "kcmc", "dw", "kcdsl", "zycllx", "ncby", "nmby", "ljcm", "kcl", "ssl", "kczj", "cszj", "spyf", "qt", "zj", "zjbfb"});

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet(sheetName);

        // 创建第一个行表头
        Row row = sheet.createRow(0);
        for (int j = 0; j < titleNameHeader.size(); j++) {
            String fieldName = titleNameHeader.get(j);
            // 创建一个单元格对象
            Cell cell = row.createCell(j);
            // 设置单元格的值
            cell.setCellValue(fieldName);
            cell.setCellStyle( createCenteredCellStyle(workbook)); // 居中对齐
        }

        // 初始化变量
        int startRow = 1; // 当前行作为合并的起始行
        String prevNum = ""; // 上一行的序号
        String prevKcmc = ""; // 上一行的矿产名称
        String prevDw = ""; // 上一行的单位
        String prevKcdsl = ""; // 上一行的矿产地数量

        for (int i = 0; i < dataList.size(); i++) {
            // 创建每一行数据
            Row rowData = sheet.createRow(i + 1);
            // 每一行数据
            NbfbFiveVo nbfbFiveVo = dataList.get(i);
            Map<String, Object> sjyjObj = BeanUtil.beanToMap(nbfbFiveVo);
            // 遍历map给单元格赋值
            int m = 0;
            for (String key : titleCodeHeader) {
                if (sjyjObj.containsKey(key)) {
                    // 创建一个单元格对象
                    Cell cell = rowData.createCell(m);
                    // 设置单元格的值
                    Object value = sjyjObj.get(key);
                    if (value instanceof Double) {
                        double doubleValue = (Double) value;
                        if (doubleValue == 0.0) {
                            cell.setCellValue("——"); // 将0.0转换成-
                        } else {
                            cell.setCellValue(doubleValue); // 设置为数字类型
                            cell.setCellType(CellType.NUMERIC); // 明确设置为数字类型
                        }
                    } else {
                        cell.setCellValue(String.valueOf(value));
                    }
                    cell.setCellStyle( createCenteredCellStyle(workbook)); // 居中对齐
                    m++;
                }
            }

            // 获取当前行的序号、矿产名称、单位和矿产地数量
            String currentNum = String.valueOf(sjyjObj.get("num"));
            String currentKcmc = String.valueOf(sjyjObj.get("kcmc"));
            String currentDw = String.valueOf(sjyjObj.get("dw"));
            String currentKcdsl = String.valueOf(sjyjObj.get("kcdsl"));

            // 检查当前行的序号、矿产名称、单位和矿产地数量是否与上一行相同
            if (i > 0 && (!currentNum.equals(prevNum) || !currentKcmc.equals(prevKcmc) || !currentDw.equals(prevDw) || !currentKcdsl.equals(prevKcdsl))) {
                // 如果不同，则合并上一个序号、矿产名称、单位和矿产地数量的单元格
                sheet.addMergedRegion(new CellRangeAddress(startRow, i , 0, 0)); // 合并序号
                sheet.addMergedRegion(new CellRangeAddress(startRow, i, 1, 1)); // 合并矿产名称
                sheet.addMergedRegion(new CellRangeAddress(startRow, i , 2, 2)); // 合并单位
                sheet.addMergedRegion(new CellRangeAddress(startRow, i , 3, 3)); // 合并矿产地数量
                startRow = i + 1; // 更新合并的起始行为当前行
            }

            prevNum = currentNum; // 更新上一行的序号
            prevKcmc = currentKcmc; // 更新上一行的矿产名称
            prevDw = currentDw; // 更新上一行的单位
            prevKcdsl = currentKcdsl; // 更新上一行的矿产地数量
        }

// 处理最后一个序号、矿产名称、单位和矿产地数量的合并
        if (dataList.size() > 0) {
            sheet.addMergedRegion(new CellRangeAddress(startRow, dataList.size(), 0, 0)); // 合并序号
            sheet.addMergedRegion(new CellRangeAddress(startRow, dataList.size(), 1, 1)); // 合并矿产名称
            sheet.addMergedRegion(new CellRangeAddress(startRow, dataList.size(), 2, 2)); // 合并单位
            sheet.addMergedRegion(new CellRangeAddress(startRow, dataList.size(), 3, 3)); // 合并矿产地数量
        }
//        // 自适应列宽
//        for (int i = 0; i < titleNameHeader.size(); i++) {
//            sheet.autoSizeColumn(i);
//        }
        workbook.write(response.getOutputStream());
        workbook.close(); // 关闭工作簿
    }

    private static CellStyle createCenteredCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        return style;
    }
}